package com.lzjn.code.common.base;

import com.lzjn.code.common.model.PageModel;
import com.lzjn.code.common.util.CountSqlHelper;
import com.lzjn.code.common.util.FieldUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CommonDao<T> extends BaseDao {

	private static Logger log = LoggerFactory.getLogger(CommonDao.class);
	private Class<?> classType;
	
	public CommonDao() {
		
		classType = (Class<?>) ((ParameterizedType) this.getClass()
				.getGenericSuperclass()).getActualTypeArguments()[0];
	}

	public PageModel queryByPage(String sql, List<Object> params, PageModel page) {
		
		Connection conn = getConnection();
		PreparedStatement psc = null;
		ResultSet rsc = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			
			String countSql = CountSqlHelper.getCountString(sql);
			
			psc = conn.prepareStatement(countSql);
			
			for(int i = 0; i < params.size(); i++) {
				
				psc.setObject(i+1, params.get(i));
			}
			
			rsc = psc.executeQuery();
			
			if(rsc.next()) {
				
				page.setRecordsTotal(rsc.getInt(1));
				page.setRecordsFiltered(rsc.getInt(1));
			}
			
			String limitSql = getLimitSql(sql, page.getStart(), page.getLength());
			
			ps = conn.prepareStatement(limitSql);
			
			for(int i = 0; i < params.size(); i++) {
				
				ps.setObject(i+1, params.get(i));
			}
			
			rs = ps.executeQuery();
			
			Field[] fields = classType.getDeclaredFields(); 
			
			List<Object> list = new ArrayList<Object>();
			
			while(rs.next()) {
				
				Object object = classType.newInstance();
				
				for(Field field : fields) {
					
					String fieldName = field.getName();
					String column = FieldUtil.formatFieldColumn(fieldName);
					
					try {
						
						rs.findColumn(column);
					} catch (SQLException e) {
						
						continue;
					}
					
					String methodName = new StringBuilder("set")
							.append(fieldName.substring(0, 1).toUpperCase())
							.append(fieldName.substring(1))
							.toString();
					
					Method method = classType.getDeclaredMethod(methodName, field.getType());
					
					method.invoke(object, rs.getObject(column));
				}
				
				list.add(object);
			}
			
			page.setData(list);
		} catch (Exception e) {
			
			log.error(e.getMessage(), e);
		} finally {
			
			try {
				
				if(rsc != null) {
					rsc.close();
				}
				if(psc != null) {
					psc.close();
				}
				if(rs != null) {
					rs.close();
				}
				if(ps != null) {
					ps.close();
				}
				if(conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return page;
	}
	
	@SuppressWarnings("unchecked")
	public List<T> query(String sql, List<Object> params) {
		
		List<T> list = new ArrayList<T>();
		
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			
			ps = conn.prepareStatement(sql);
			
			if(params != null) {
				
				for(int i = 0; i < params.size(); i++) {
					
					ps.setObject(i+1, params.get(i));
				}
			}
			
			rs = ps.executeQuery();
			
			Field[] fields = classType.getDeclaredFields(); 
			
			while(rs.next()) {
				
				Object object = classType.newInstance();
				
				for(Field field : fields) {
					
					String fieldName = field.getName();
					String column = FieldUtil.formatFieldColumn(fieldName);
					
					try {
						
						rs.findColumn(column);
					} catch (SQLException e) {
						
						continue;
					}
					
					String methodName = new StringBuilder("set")
							.append(fieldName.substring(0, 1).toUpperCase())
							.append(fieldName.substring(1))
							.toString();
					
					Method method = classType.getDeclaredMethod(methodName, field.getType());
					
					method.invoke(object, rs.getObject(column));
				}
				
				list.add((T)object);
			}
			
		} catch (Exception e) {
			
			log.error(e.getMessage(), e);
		} finally {
			
			try {
				
				if(rs != null) {
					rs.close();
				}
				if(ps != null) {
					ps.close();
				}
				if(conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public T queryById(String sql, String id) {
		
		Object object = null;
		
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, id);
			
			rs = ps.executeQuery();
			
			Field[] fields = classType.getDeclaredFields(); 
			
			if(rs.next()) {
				
				object = classType.newInstance();
				
				for(Field field : fields) {
					
					String fieldName = field.getName();
					String column = FieldUtil.formatFieldColumn(fieldName);
					
					try {
						
						rs.findColumn(column);
					} catch (SQLException e) {
						
						continue;
					}
					
					String methodName = new StringBuilder("set")
							.append(fieldName.substring(0, 1).toUpperCase())
							.append(fieldName.substring(1))
							.toString();
					
					Method method = classType.getDeclaredMethod(methodName, field.getType());
					
					method.invoke(object, rs.getObject(column));
				}
			}
		} catch (Exception e) {
			
			log.error(e.getMessage(), e);
		} finally {
			
			try {
				
				if(rs != null) {
					rs.close();
				}
				if(ps != null) {
					ps.close();
				}
				if(conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return (T)object;
	}
	
	public void executeUpdate(String sql, List<Object> params) throws Exception {
		
		Connection conn = getConnection();
		PreparedStatement ps = null;
		
		try {
			
			ps = conn.prepareStatement(sql.toString());
			
			if(params != null) {
				
				for(int i = 0; i < params.size(); i++) {
					
					ps.setObject(i+1, params.get(i));
				}
			}
			
			ps.executeUpdate();
		} catch (Exception e) {
			
			log.error(e.getMessage(), e);
			throw e;
		} finally {
			
			try {
				
				if(ps != null) {
					ps.close();
				}
				if(conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void executeUpdate(String sql, List<Object> params, Connection conn) throws Exception {
		
		PreparedStatement ps = null;
		
		try {
			
			ps = conn.prepareStatement(sql.toString());
			
			if(params != null) {
				
				for(int i = 0; i < params.size(); i++) {
					
					ps.setObject(i+1, params.get(i));
				}
			}
			
			ps.executeUpdate();
		} catch (Exception e) {
			
			log.error(e.getMessage(), e);
			throw e;
		} finally {
			
			try {
				
				if(ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void executeBatchUpdate(String sql, List<List<Object>> params, Connection conn) throws Exception {
		
		PreparedStatement ps = null;
		
		try {
			
			ps = conn.prepareStatement(sql.toString());
			
			for(int i = 0; i < params.size(); i++) {
				
				List<Object> condition = params.get(i);
				
				for(int j = 0; j < condition.size(); j++) {
					
					ps.setObject(j+1, condition.get(j));
				}
				
				ps.addBatch();
			}
			
			ps.executeBatch();
		} catch (Exception e) {
			
			log.error(e.getMessage(), e);
			throw e;
		} finally {
			
			try {
				
				if(ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}
